package PracticeFinal;

import javax.swing.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Arrays;
import java.util.Properties;

public class JDBCUtils {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    static {
        try {
            Properties pro = new Properties();
            String path = JDBCUtils.class.getClassLoader().getResource("jdbc.properties").getFile();
            pro.load(new FileInputStream(path));
            driver = pro.getProperty("driver");
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            Class.forName(driver);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public static void checkUser(String t_user, String t_pwd){
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        String username = null;
        String password = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select * from user where username=?";
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, t_user);

            resultSet = pstmt.executeQuery();

            if (resultSet.next()) {
                username = resultSet.getString(2);
                password = resultSet.getString(3);
                if (t_pwd.equals(password)){
                    new MainFrame(username);
                }else {
                    System.out.println(t_pwd);
                    JOptionPane.showMessageDialog(null,"密码出错", "警告",JOptionPane.ERROR_MESSAGE);
                }
            }else {
                JOptionPane.showMessageDialog(null,"用户名不存在", "警告",JOptionPane.ERROR_MESSAGE);
            }

        } catch (SQLException e2) {
            throw new RuntimeException(e2);
        }
    }

    //获取所有数据
    public static Object[][] getRows(String sql, Connection connection){

        Object[][] row = new Object[50][6];
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);

            resultSet = pstmt.executeQuery();
            int i = 0;
            while (resultSet.next()){
                String yearMonth = resultSet.getString(1);
                String employeeId = resultSet.getString(2);
                String employeeName = resultSet.getString(3);
                int wage = resultSet.getInt(4);
                int allowance = resultSet.getInt(5);
                row[i][0]=yearMonth;
                row[i][1]=employeeId;
                row[i][2]=employeeName;
                row[i][3]=wage;
                row[i][4]=allowance;
                row[i][5]=wage+allowance;
                i++;
                System.out.println(yearMonth+employeeId+employeeName+wage+allowance);
            }
        } catch (SQLException exception) {
            throw new RuntimeException(exception);
        }
        return row;

    }
    //获取符合条件的数据
    public static Object[][] getRowsById(String sql, Connection connection, String id){

        Object[][] row = new Object[50][6];
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,id);

            resultSet = pstmt.executeQuery();
            int i = 0;
            while (resultSet.next()){
                String yearMonth = resultSet.getString(1);
                String employeeId = resultSet.getString(2);
                String employeeName = resultSet.getString(3);
                int wage = resultSet.getInt(4);
                int allowance = resultSet.getInt(5);
                row[i][0]=yearMonth;
                row[i][1]=employeeId;
                row[i][2]=employeeName;
                row[i][3]=wage;
                row[i][4]=allowance;
                row[i][5]=wage+allowance;
                i++;
                System.out.println(yearMonth+employeeId+employeeName+wage+allowance);
            }
        } catch (SQLException exception) {
            throw new RuntimeException(exception);
        }
        return row;

    }

    //获取符合条件的数据
    public static Object[][] getRowsByTime(String sql, Connection connection, String fromDate){

        Object[][] row = new Object[50][6];
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, fromDate);

            resultSet = pstmt.executeQuery();
            int i = 0;
            while (resultSet.next()){
                String yearMonth = resultSet.getString(1);
                String employeeId = resultSet.getString(2);
                String employeeName = resultSet.getString(3);
                int wage = resultSet.getInt(4);
                int allowance = resultSet.getInt(5);
                row[i][0]=yearMonth;
                row[i][1]=employeeId;
                row[i][2]=employeeName;
                row[i][3]=wage;
                row[i][4]=allowance;
                row[i][5]=wage+allowance;
                i++;
                System.out.println(yearMonth+employeeId+employeeName+wage+allowance);
            }
        } catch (SQLException exception) {
            throw new RuntimeException(exception);
        }
        return row;

    }

    public static Object[][] updateById(String sql, Connection connection, String yearMonth, String id, String name, int wage, int allowance){

        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, yearMonth);
            pstmt.setString(2, name);
            pstmt.setInt(3, wage);
            pstmt.setInt(4, allowance);
            pstmt.setString(5, id);

            pstmt.execute();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        //程序复用
        return JDBCUtils.getRows("select * from employee",connection);
    }

    public static Object[][] deleteById(String sql, Connection connection, String id){

        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1,id);

            pstmt.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return JDBCUtils.getRows("select * from employee",connection);
    }

    public static Object[][] insertById(String sql, Connection connection, String yearMonth, String id, String name, int wage, int allowance){
        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, yearMonth);
            pstmt.setString(2, id);
            pstmt.setString(3, name);
            pstmt.setInt(4, wage);
            pstmt.setInt(5, allowance);

            pstmt.execute();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        //程序复用
        return JDBCUtils.getRows("select * from employee",connection);
    }

    public static Connection getConnection() {
        try {
            Connection connection = DriverManager.getConnection(url,user,password);
            return connection;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void close(Statement statement, Connection connection){
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
